<?php
#
# UNLVSpatial: a dmBridge module for spatial content
#
# Copyright © 2011 Board of Regents of the Nevada System of Higher
# Education, on behalf of the University of Nevada, Las Vegas
#

/**
 * A "generic" data store class that returns some generic, cross-DBMS-compatible
 * SQL commands.
 *
 * @author Alex Dolski <alex.dolski@unlv.edu>
 * @license http://www.opensource.org/licenses/mit-license.php
 */
abstract class UNLVSpatialGenericDataStore extends DMMySQLDataStore {

	/**
	 * @return SQL command string
	 */
	public static function getInsertObjectSQL() {
		return "INSERT INTO spatial_object(alias, ptr, lat, `long`, lat_n, lat_s, long_w, long_e)
			VALUES(:alias, :ptr, :lat, :long, :lat_n, :lat_s, :long_w, :long_e);";
	}

	/**
	 * @return SQL command string
	 */
	public static function getNumObjectsInCollectionSQL() {
		return "SELECT COUNT(id) AS count
			FROM spatial_object
			WHERE alias = :alias";
	}

	/**
	 * @return SQL command string
	 */
	public static function getObjectSQL() {
		$select = "SELECT lat, `long`, lat_n, lat_s, long_w, long_e ";
		$from = "FROM spatial_object ";
		$where = "WHERE alias = :alias AND ptr = :ptr ";
		$limit = "LIMIT 1";

		return $select . $from . $where . $limit . ";";
	}

	/**
	 * @param UNLVSpatialQuery query
	 * @return SQL command string
	 */
	public static function getCountSQL(UNLVSpatialQuery $query) {
		$select = "SELECT COUNT(id) AS count ";
		$from = "FROM spatial_object ";
		$where = self::getWhere($query);		
		return $select . $from . $where . ";";
	}

	/**
	 * @param UNLVSpatialQuery query
	 * @return SQL command string
	 */
	public static function getSQL(UNLVSpatialQuery $query) {
		$select = "SELECT *,
				(long_e - long_w) * (lat_n - lat_s) AS area,
				lat_s + ((lat_n - lat_s) * 0.5) AS clat,
				long_w + ((long_e - long_w) * 0.5) AS clong ";
		$from = "FROM spatial_object ";
		$where = self::getWhere($query);

		$limit = sprintf(" LIMIT %d ", $query->getNumResultsPerPage());
		$offset = sprintf("OFFSET %d ",
				($query->getPage() - 1) * $query->getNumResultsPerPage());
		
		return $select . $from . $where . $limit . $offset . ";";
	}

	private static function getWhere(UNLVSpatialQuery $query) {
		$where = "WHERE 1=1 ";
		foreach ($query->getQueryTerms() as $term) {
			switch ($term->getMatchingMode()) {
			case "outside":
				switch ($term->getExampleObject()->getSpatialModelClassName()) {
				case "UNLVSpatialRectObject":
					$where .= sprintf("AND ((lat_s > %f OR lat_n < %f)
							OR (long_w > %f OR long_e < %f))",
							$term->getExampleObject()->getSpatialNorthLat(),
							$term->getExampleObject()->getSpatialSouthLat(),
							$term->getExampleObject()->getSpatialEastLong(),
							$term->getExampleObject()->getSpatialWestLong());
					break;
				case "UNLVSpatialPointObject":
					$where .= sprintf("AND (lat > %f OR `long` < %f)",
							$term->getExampleObject()->getSpatialLat(),
							$term->getExampleObject()->getSpatialLong());
					break;
				}
				break;
			case "intersect":
				switch ($term->getExampleObject()->getSpatialModelClassName()) {
				case "UNLVSpatialRectObject":
					$where .= sprintf("AND (((long_e >= %f AND long_e <= %f)
								OR (long_w <= %f AND long_w >= %f))
							AND ((lat_n >= %f AND lat_n <= %f)
								OR (lat_s <= %f AND lat_s >= %f)))",
							$term->getExampleObject()->getSpatialWestLong(),
							$term->getExampleObject()->getSpatialEastLong(),
							$term->getExampleObject()->getSpatialEastLong(),
							$term->getExampleObject()->getSpatialWestLong(),
							$term->getExampleObject()->getSpatialSouthLat(),
							$term->getExampleObject()->getSpatialNorthLat(),
							$term->getExampleObject()->getSpatialNorthLat(),
							$term->getExampleObject()->getSpatialSouthLat());
					break;
				case "UNLVSpatialPointObject":
					$where .= sprintf("AND (lat >= %f AND lat <= %f
								AND `long` >= %f AND `long` <= %f)",
							$term->getExampleObject()->getSpatialLat(),
							$term->getExampleObject()->getSpatialLat(),
							$term->getExampleObject()->getSpatialLong(),
							$term->getExampleObject()->getSpatialLong());
					break;
				}
				break;
			case "within":
				switch ($term->getExampleObject()->getSpatialModelClassName()) {
				case "UNLVSpatialRectObject":
					$where .= sprintf("AND (lat_s >= %f AND long_w >= %f
								AND lat_n <= %f AND long_e <= %f)",
							$term->getExampleObject()->getSpatialSouthLat(),
							$term->getExampleObject()->getSpatialWestLong(),
							$term->getExampleObject()->getSpatialNorthLat(),
							$term->getExampleObject()->getSpatialEastLong());
					break;
				case "UNLVSpatialPointObject":
					$where .= sprintf("AND (lat >= %f AND lat <= %f
								AND `long` >= %f AND `long` <= %f)",
							$term->getExampleObject()->getSpatialLat(),
							$term->getExampleObject()->getSpatialLat(),
							$term->getExampleObject()->getSpatialLong(),
							$term->getExampleObject()->getSpatialLong());
					break;
				}
				break;
			}
		}
		return $where;
	}

	/**
	 * @return SQL command string
	 */
	public static function getUpdateObjectSQL() {
		return "UPDATE spatial_object
			SET lat = :lat,
				`long` = :long,
				lat_n = :lat_n,
				lat_s = :lat_s,
				long_w = :long_w,
				long_e = :long_e
			WHERE alias = :alias AND ptr = :ptr";
	}

}
